﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CQIE.TES.Model;

namespace CQIE.TES.DAL
{
    /// <summary>
    /// 操作实例 示例
    /// </summary>
    public abstract class DALBase<T> where T:class
    {
        private void test()
        {
            using (MyDB db = new MyDB())
            {
                #region 添加

                // 添加
                db.Teacher.Add(new Teacher() {Name = "adsawd"});

                // 保存到数据库
                db.SaveChanges();

                #endregion

                #region 删除

                {
                    // 通过主键查询
                    Teacher model = db.Teacher.Find(1);
                    db.Teacher.Remove(model);

                    // 保存数据库
                    db.SaveChanges();
                }

                #endregion

                #region 修改

                {
                    var query = from t in db.Teacher
                        where t.Name == "adsawd"
                        select t;
                    query.First().Name = "修改";

                    db.SaveChanges();
                }


                #endregion

                #region 查询

                {
                    // 1.LINQ
                    var query = (from t in db.Teacher
                        where t.Name == "adsawd"
                        select t).ToList();

                    // 2. 扩展方法 lambda
                    var query3 = db.Teacher.Where(t => t.Name.Contains("adsawd"));

                    // 直接获取 第一个元素
                    Teacher tea = query[0];

                    // 如果 查询结果 query 没有 ToLost() 获取第一个元素
                    var query1 = from t in db.Teacher
                        where t.Name == "adsawd"
                        select t;

                    Teacher tea2 = query1.First();


                    foreach (Teacher teacher in query)
                    {
                        
                        // 操作数据

                    }
                }


                #endregion

            }
        }

        private string className = typeof(T).Name;

        public T FindByID(int id)
        {
            using (MyDB db = new MyDB())
            {
                var query = db.Database.SqlQuery<T>($"select * from {className} where ID=@ID",new SqlParameter("@ID",id)).ToList();

                if (query.Count==0)
                {
                    return null;
                }
                
                return query.First();
            }
        }

        /// <summary>
        /// 通过主键删除  未测试
        /// </summary>
        /// <param name="id"></param>
        /// <returns>删除成功返回true</returns>
        public bool DeleteByID(int id)
        {
            using (MyDB db = new MyDB())
            {
                int changeNum = db.Database.ExecuteSqlCommand($"delete from {className} where ID=@ID",
                    new SqlParameter("@ID", id));

                if (changeNum>0)
                {
                    return true;
                }

                return false;
            }
        }
    }
}
